﻿using Dapper;
using SetSailModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SetSailDal
{
    public class ZDGLDal
    {
        public static SetSailModel.QH_ZDGL GetModel(string ID)
        {
            string sql = string.Format($@"SELECT * FROM [dbo].[QH_ZDGL] with (nolock) where id='{ID}'");
            using (DBHelper help = new DBHelper())
            {
                var ls = help.DbContext.Query<QH_ZDGL>(sql) as List<QH_ZDGL>;
                if (ls.Count > 0)
                {
                    return ls[0];
                }
                else
                {
                    return new QH_ZDGL();
                }
            }
        }

        /// <summary>
        /// 字典分页
        /// </summary>
        /// <returns></returns>
        public static ResultModel<List<SetSailModel.QH_ZDGL>> GetZidianPageList(int pageIndex, int pageSize, string where, string sort = "id")
        {
            ResultModel<List<QH_ZDGL>> result = new ResultModel<List<QH_ZDGL>>();
            try
            {
                int skip = 1;//从第几条开始
                int take = pageIndex * pageSize;//到第几条结束
                if (pageIndex > 0)
                {
                    skip = ((pageIndex - 1) * pageSize) + 1;
                }
                where = where.Length > 0 ? where + " AND SCBJ=0" : " SCBJ=0";
                //string sql = string.Format(@"SELECT *,'删除' btn FROM [dbo].[QH_ZDGL] WHERE SCBJ=0");
                string sql = string.Format($@"SELECT * FROM
                            (SELECT ROW_NUMBER() OVER(ORDER BY {sort}) AS RowNum,*,'删除' btn  FROM  [dbo].[QH_ZDGL] with (nolock) WHERE {where}) AS result
                            WHERE  RowNum >= {skip}   AND RowNum <= {take} ORDER BY {sort}");
                string sql_count = $"SELECT COUNT(1) FROM [dbo].[QH_ZDGL] with (nolock) where {where}";
                using (DBHelper help = new DBHelper())
                {
                    result.Data = help.DbContext.Query<QH_ZDGL>(sql) as List<QH_ZDGL>;
                    result.Count = (int)help.DbContext.ExecuteScalar(sql_count);
                }
            }
            catch (Exception ex)
            {
                result.Code = ResultCode.Fail;
                result.Message = ex.Message;
            }
            return result;
        }

        /// <summary>
        /// 字典明细列表
        /// </summary>
        /// <returns></returns>
        public static ResultModel<List<SetSailModel.QH_ZDMX>> GetZDMXList(string where = "")
        {
            ResultModel<List<QH_ZDMX>> result = new ResultModel<List<QH_ZDMX>>();
            try
            {
                where = where.Length > 0 ? where + " AND SCBJ=0" : " SCBJ=0";
                string sql = string.Format(@"SELECT *,'删除' as btn FROM [dbo].[QH_ZDMX] with (nolock) WHERE {0}", where);
                using (DBHelper help = new DBHelper())
                {
                    result.Data = help.DbContext.Query<QH_ZDMX>(sql) as List<QH_ZDMX>;
                }
            }
            catch (Exception ex)
            {
                result.Code = ResultCode.Fail;
                result.Message = ex.Message;
            }
            return result;
        }
        public static int GetCount(string where)
        {
            try
            {
                where = where.Length > 0 ? where + " AND SCBJ=0" : " SCBJ=0";
                string sql = string.Format(@"SELECT count(1) count FROM [dbo].[QH_ZDGL] with (nolock) WHERE {0}", where);
                using (DBHelper help = new DBHelper())
                {
                    return (int)help.DbContext.ExecuteScalar(sql);
                }
            }
            catch (Exception ex)
            {
                return 0;
            }
            return 0;
        }

        /// <summary>
        /// 新增字典
        /// </summary>
        /// <param name="zdglModel">字典model</param>
        /// <param name="zdmxList">明细字典集合</param>
        /// <returns></returns>
        public static ResultModel<int> SaveZDGL(QH_ZDGL zdglModel, List<QH_ZDMX> zdmxList)
        {
            ResultModel<int> result = new ResultModel<int>();
            try
            {
                string sql = @"INSERT INTO [dbo].[QH_ZDGL]([ID], [ZDDM], [ZDMC], [CJSJ], [SCBJ]) 
            VALUES (@ID, @ZDDM, @ZDMC, GETDATE(), 0);";
                string sqlmx = @"INSERT INTO [dbo].[QH_ZDMX]([ID], [ZDID], [MXDM], [MXMC], [MXZ], [CJSJ], [SCBJ]) 
            VALUES (@ID, @ZDID, @MXDM, @MXMC, @MXZ, GETDATE(), 0);";
                zdmxList.ForEach(obj =>
                {
                    obj.ZDID = zdglModel.ID;
                    obj.ID = SetSailCommon.CommonHelper.GetNewGuid();
                });
                using (DBHelper help = new DBHelper())
                {
                    help.DbContext.Execute(sqlmx, zdmxList);
                    if (help.DbContext.Execute(sql, zdglModel) == 0)
                    {
                        result.Code = ResultCode.Fail;
                        result.Message = "保存失败。";
                    }
                }
            }
            catch (Exception ex)
            {
                result.Code = ResultCode.Fail;
                result.Message = ex.Message;
            }
            return result;
        }

        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="zdglModel">字典model</param>
        /// <param name="zdmxList">明细字典集合</param>
        /// <returns></returns>
        public static ResultModel<int> EditZDGL(QH_ZDGL zdglModel, List<QH_ZDMX> zdmxList)
        {
            ResultModel<int> result = new ResultModel<int>();
            try
            {
                string sql = string.Format(@"UPDATE [dbo].[QH_ZDGL] SET ZDDM='{0}',ZDMC='{1}' WHERE ID = '{2}'", zdglModel.ZDDM, zdglModel.ZDMC, zdglModel.ID);
                List<QH_ZDMX> addlist = zdmxList.Where(obj => obj.ID == null || obj.ID.Length == 0).ToList();

                string sqlmx = @"INSERT INTO [dbo].[QH_ZDMX]([ID], [ZDID], [MXDM], [MXMC], [MXZ], [CJSJ], [SCBJ]) 
            VALUES (@ID, @ZDID, @MXDM, @MXMC, @MXZ, GETDATE(), 0);";
                List<QH_ZDMX> editlist = zdmxList.Where(obj => obj.ID != null && obj.ID.Length > 0).ToList();
                string editmx = @"UPDATE [dbo].[QH_ZDMX] SET MXDM=@MXDM,MXMC=@MXMC,MXZ=@MXZ where ID=@ID";

                using (DBHelper help = new DBHelper())
                {
                    //新增字典明细
                    if (addlist!=null && addlist.Count > 0)
                    {
                        addlist.ForEach(obj =>
                        {
                            obj.ZDID = zdglModel.ID;
                            obj.ID = SetSailCommon.CommonHelper.GetNewGuid();
                        });
                        result.Count = help.DbContext.Execute(sqlmx, addlist);
                    }
                    //修改字典明细
                    if (editlist!=null && editlist.Count > 0)
                        result.Count = help.DbContext.Execute(editmx, editlist);
                    //修改字典
                    result.Count = help.DbContext.Execute(sql);
                    if (result.Count == 0)
                    {
                        result.Code = ResultCode.Fail;
                        result.Message = "删除失败";
                    }
                }
            }
            catch (Exception ex)
            {
                result.Code = ResultCode.Fail;
                result.Message = ex.Message;
            }
            return result;
        }

        /// <summary>
        /// 字典删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static ResultModel<int> EditZDGL(string ID)
        {
            ResultModel<int> result = new ResultModel<int>();
            try
            {
                string sql = string.Format(@"UPDATE [dbo].[QH_ZDGL] SET SCBJ=1 WHERE ID = '{0}'", ID);
                using (DBHelper help = new DBHelper())
                {
                    result.Count = help.DbContext.Execute(sql);
                    if (result.Count == 0)
                    {
                        result.Code = ResultCode.Fail;
                        result.Message = "删除失败";
                    }
                }
            }
            catch (Exception ex)
            {
                result.Code = ResultCode.Fail;
                result.Message = ex.Message;
            }
            return result;
        }

        /// <summary>
        /// 字典明细删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static ResultModel<int> EditZDMX(string ID)
        {
            ResultModel<int> result = new ResultModel<int>();
            try
            {
                string sql = string.Format(@"UPDATE [dbo].[QH_ZDMX] SET SCBJ=1 WHERE ID = '{0}'", ID);
                using (DBHelper help = new DBHelper())
                {
                    result.Count = help.DbContext.Execute(sql);
                    if (result.Count == 0)
                    {
                        result.Code = ResultCode.Fail;
                        result.Message = "删除失败";
                    }
                }
            }
            catch (Exception ex)
            {
                result.Code = ResultCode.Fail;
                result.Message = ex.Message;
            }
            return result;
        }
    }
}
